Imports System.Xml
Imports System.Text
Imports System.IO
Imports System.Web

Friend Class DBInstall
    Inherits DBBase

    Private Const Q As String = Chr(34)

    Public Sub CreateSchema( _
                    ByVal sConnection As String, _
                    ByRef OUTPUT_ErrorCount As Integer)

        Dim sPath As String = AppDomain.CurrentDomain.BaseDirectory & "SchemaList.xml"
        Dim oSReader As New StreamReader(sPath)
        Dim oXReader As New XmlTextReader(oSReader)

        Do While oXReader.Read
            If oXReader.Name = "script" Then
                Dim sSchemaPath As String = AppDomain.CurrentDomain.BaseDirectory & oXReader.GetAttribute("path")
                Dim sType As String = oXReader.GetAttribute("type")
                Dim sName As String = oXReader.GetAttribute("name")

                HttpContext.Current.Response.Write("<br/>Executing script: " & sName)
                HttpContext.Current.Response.Flush()

                Try
                    If ExecuteSchema(sSchemaPath, sType, sName, sConnection) = False Then
                        OUTPUT_ErrorCount = OUTPUT_ErrorCount + 1
                    End If
                Catch oEX As Exception
                    OUTPUT_ErrorCount = OUTPUT_ErrorCount + 1

                    HttpContext.Current.Response.Write("<br/>The following error occurred while processing script: " & sName)
                    HttpContext.Current.Response.Write("<div style=" & Q & "margin-left: 50px;" & Q & ">")
                    HttpContext.Current.Response.Write("Type: " & oEX.GetType.ToString)
                    HttpContext.Current.Response.Write("Message: " & oEX.Message.ToString)

                    HttpContext.Current.Response.Write("</div>")

                    HttpContext.Current.Response.Flush()
                End Try

                Try
                    If sType = "table" _
                            AndAlso Not oXReader.IsEmptyElement Then

                        '---Add base data
                        AddBaseData(sName, oXReader.ReadSubtree, sConnection)
                    End If

                Catch oEX As Exception
                    OUTPUT_ErrorCount = OUTPUT_ErrorCount + 1

                    HttpContext.Current.Response.Write("<br/>The following error occurred while adding base data for script: " & sName)
                    HttpContext.Current.Response.Write("<div style=" & Q & "margin-left: 50px;" & Q & ">")
                    HttpContext.Current.Response.Write("Type: " & oEX.GetType.ToString)
                    HttpContext.Current.Response.Write("Message: " & oEX.Message.ToString)

                    HttpContext.Current.Response.Write("</div>")

                    HttpContext.Current.Response.Flush()
                End Try
            End If
        Loop

        oXReader.Close()
        oSReader.Close()
    End Sub

    Private Function ExecuteSchema( _
                        ByVal sSchemaPath As String, _
                        ByVal sType As String, _
                        ByVal sName As String, _
                        ByVal sConnection As String) As Boolean

        Dim bSuccess As Boolean = True

        '---If object already exists, then drop it.
        If sType <> "index" Then
            Dim sSQL As New StringBuilder("if exists(select * from ")
            sSQL.Append(" sysobjects a join sysusers b on b.[uid]=a.[uid]")
            sSQL.Append(" where b.[name] + '.' + a.[name]='" & sName & "'")
            Select Case sType
                Case "table"
                    sSQL.Append(" and a.[type]='u') drop table " & sName)

                Case "sproc"
                    sSQL.Append(" and a.[type]='p') drop proc " & sName)

                Case "view"
                    sSQL.Append(" and a.[type]='v') drop view " & sName)

                Case "function"
                    sSQL.Append(" and a.[type]='fn') drop function " & sName)
            End Select
            MyBase.ExecuteNonQuery(sSQL.ToString, sConnection)
        End If

        '---Execute schema to create object
        Dim sText As New StringBuilder
        'Dim oSchemaStream As New StreamReader(sSchemaPath, System.Text.Encoding.Unicode, True)
        Dim oSchemaStream As New StreamReader(sSchemaPath, True)

        Do While Not oSchemaStream.EndOfStream
            sText.Append(oSchemaStream.ReadLine & "  " & vbCrLf)
        Loop
        oSchemaStream.Close()
        oSchemaStream.Dispose()

        Try
            MyBase.ExecuteNonQuery(sText.ToString, sConnection)

        Catch oEX As Exception
            bSuccess = False

            HttpContext.Current.Response.Write("<br/>The following error occurred while executing script: " & sName)
            HttpContext.Current.Response.Write("<div style=" & Q & "margin-left: 50px;" & Q & ">")
            HttpContext.Current.Response.Write("Type: " & oEX.GetType.ToString)
            HttpContext.Current.Response.Write("Message: " & oEX.Message.ToString)

            HttpContext.Current.Response.Write("<pre>")
            HttpContext.Current.Response.Write(sText.ToString)
            HttpContext.Current.Response.Write("</pre>")

            HttpContext.Current.Response.Write("</div>")

            HttpContext.Current.Response.Flush()
        End Try

        Return bSuccess
    End Function

    Private Sub AddBaseData( _
                    ByVal sName As String, _
                    ByRef oXReader As XmlReader, _
                    ByVal sConnection As String)

        Dim sInsertSQL As New StringBuilder
        Dim sDataSQL As New StringBuilder

        Dim bIsFirstColumn As Boolean = True

        Do While oXReader.Read
            If oXReader.IsStartElement Then
                If oXReader.Name = "columns" Then
                    '---Start composing standard insert script.
                    sInsertSQL = New StringBuilder("insert into " & sName & "(")

                ElseIf oXReader.Name = "column" Then
                    If bIsFirstColumn = False Then sInsertSQL.Append(",")
                    sInsertSQL.Append("[" & oXReader.GetAttribute("name") & "]")
                    bIsFirstColumn = False

                ElseIf oXReader.Name = "row" Then
                    '---Add a new row
                    sDataSQL = New StringBuilder(") values (")
                    sDataSQL.Append(ValueToDBValue(oXReader.GetAttribute(0)))

                    For i As Integer = 1 To oXReader.AttributeCount - 1
                        sDataSQL.Append("," & ValueToDBValue(oXReader.GetAttribute(i)))
                    Next

                    sDataSQL.Append(")")

                    MyBase.ExecuteNonQuery(sInsertSQL.ToString & sDataSQL.ToString, sConnection)
                End If
            End If
        Loop
    End Sub
End Class
